/**
 *  Copyright (C) 2008-2013  Telosys project org. ( http://www.telosys.org/ )
 *
 *  Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, Version 3.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *          http://www.gnu.org/licenses/lgpl.html
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package org.telosys.persistence.sql;

import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Tool to run database scripts ( SQL files )
 */
public class SqlScriptExecutor {

	public final static boolean  AUTOCOMMIT    = true ;
	public final static boolean  NO_AUTOCOMMIT = false ;
	
    private Connection  connection;

    private boolean     autoCommit;

    private PrintWriter outputWriter = new PrintWriter(System.out);
    private PrintWriter errorWriter  = new PrintWriter(System.err);

    private String   commandDelimiter         = ";" ;  //  ";" by default 
    private boolean  fullLineCommandDelimiter = false; // Command delimiter alone in the line 

    /**
     * Constructor
     * @param connection - the connection to be used
     * @param autoCommit - the auto-commit indicator 
     */
    public SqlScriptExecutor(Connection connection, boolean autoCommit )
    {
        this.connection = connection;
        this.autoCommit = autoCommit;
    }

    /**
     * Set a specific command delimiter ( if different from ";" )
     * @param delimiter - the specific delimiter 
     * @param fullLineDelimiter - true if it must be alone on a line
     */
    public void setCommandDelimiter(String delimiter, boolean fullLineDelimiter) 
    {
        this.commandDelimiter = delimiter;
        this.fullLineCommandDelimiter = fullLineDelimiter;
    }

    /**
     * Set the standard output writer
     *
     * @param outputWriter - the new value of the logWriter property
     */
    public void setOutputWriter(PrintWriter outputWriter) {
        this.outputWriter = outputWriter;
    }

    /**
     * Set the error output writer
     *
     * @param errorWriter - the new value of the errorLogWriter property
     */
    public void setErrorWriter(PrintWriter errorWriter) {
        this.errorWriter = errorWriter;
    }

    /**
     * Executes the given SQL script file 
     * 
     * @param fileName
     * @throws IOException
     * @throws SQLException
     */
    public void executeScript(String fileName) throws IOException, SQLException 
    {
//    	if ( FileUtil.exists(fileName) ) {
    		FileReader fr = new FileReader(fileName);
//    		SqlScriptRunner r = new SqlScriptRunner(connection, true);
//    		r.runScript(fr);
    		this.executeScript(fr);
    		fr.close();
//    	}
//    	else {
//    		throw new FileNotFoundException("File : '" + fileName + "'");
//    	}
    }
    
    /**
     * Executes the given SQL script (read in using the Reader parameter)
     *
     * @param reader - the source of the script
     */
    public void executeScript(Reader reader) throws IOException, SQLException {
        try {
            boolean originalAutoCommit = connection.getAutoCommit();
            try {
                if (originalAutoCommit != this.autoCommit) {
                    connection.setAutoCommit(this.autoCommit);
                }
                runScript(connection, reader);
            } finally {
                connection.setAutoCommit(originalAutoCommit);
            }
        } catch (IOException e) {
            throw e;
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException("Error running script.  Cause: " + e, e);
        }
    }

    /**
     * Runs an SQL script (read in using the Reader parameter) with the
     * connection passed in
     *
     * @param con - the connection to use 
     * @param sqlScriptReader - the source of the script
     * @throws SQLException
     * @throws IOException
     */
    private void runScript(Connection con, Reader sqlScriptReader) throws IOException, SQLException 
    {
        StringBuffer command = null;
        try 
        {
            LineNumberReader lineReader = new LineNumberReader(sqlScriptReader);
            String line = null;
            while ((line = lineReader.readLine()) != null) 
            {
                if (command == null) {
                    command = new StringBuffer();
                }
                
                String trimmedLine = line.trim();
                if (trimmedLine.startsWith("--")) 
                {
                    println(trimmedLine);
                } 
                else if (trimmedLine.startsWith("//")) 
                {
                	// Nothing to do
                } 
                else if (trimmedLine.length() == 0 ) 
                {
                	// Nothing to do
                }
//                else if (trimmedLine.length() < 1
//                        || trimmedLine.startsWith("//")) 
//                {
//                    // Do nothing
//                } 
//                else if (trimmedLine.length() < 1
//                        || trimmedLine.startsWith("--")) 
//                {
//                    // Do nothing
//                } 
                else if (!fullLineCommandDelimiter
                        && trimmedLine.endsWith(getDelimiter())
                        || fullLineCommandDelimiter
                        && trimmedLine.equals(getDelimiter())) 
                {
                    command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
                    command.append(" ");
                    
                    
                    String sqlStatement = command.toString();
                    
//                    Statement statement = con.createStatement();
//
//                    println(sqlStatement);
//
//                    boolean hasResultSet = false;
//                   	hasResultSet = statement.execute(sqlStatement);
//
//                    if (autoCommit && !con.getAutoCommit()) {
//                        con.commit();
//                    }
//                    
//                    if ( hasResultSet ) 
//                    {
//                    	printResultSet( statement.getResultSet() );
//                    }
//                    else {
//                    	println("OK (no result set)");
//                    }
//                    
//                    try {
//                        statement.close();
//                    } catch (Exception e) {
//                        // Ignore to workaround a bug in DBCP
//                    }
                    executeSqlStatement( con, sqlStatement) ;
                    
                    command = null; // end 
                    Thread.yield();
                    
                } else {
                    command.append(line);
                    command.append(" ");
                }
            }
            
            //--- Global commit if not "auto-commit mode"
            if (!autoCommit) {
                con.commit();
            }
        } catch (SQLException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            throw e;
        } catch (IOException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            throw e;
        } finally {
            con.rollback();
            flush();
        }
    }

    private String getDelimiter() {
        return commandDelimiter;
    }

    private void executeSqlStatement(Connection con, String sqlStatement) throws SQLException
    {
        Statement statement = con.createStatement();

        println(sqlStatement);

        boolean hasResultSet = false;
       	hasResultSet = statement.execute(sqlStatement);

       	//--- Commit if "auto-commit" mode
        if ( autoCommit && !con.getAutoCommit() ) {
            con.commit();
        }
        
        //--- Print the command result
        if ( hasResultSet ) 
        {
        	printResultSet( statement.getResultSet() );
        }
        else {
        	println("OK (no result set)");
        }
    	println(""); // void line between each command
        
        try {
            statement.close();
        } catch (Exception e) {
            // Ignore to workaround a bug in DBCP
        }
    	
    }
    
    private void printResultSet(ResultSet rs) throws SQLException
    {
        if (outputWriter != null) {
	        if ( rs != null ) 
	        {
	            ResultSetMetaData md = rs.getMetaData();
	            int cols = md.getColumnCount();
	            //--- Print header line
	            for (int i = 1; i <= cols; i++) {
	                String name = md.getColumnLabel(i);
	                print(name + "\t");
	            }
	            println("");
	
	            while (rs.next()) {
	            	//--- Print line of data
	                for (int i = 1; i <= cols; i++) {
	                    String value = rs.getString(i);
	                    print(value + "\t");
	                }
	                println("");
	            }
	        }
	        else
	        {
	            println("ResultSet is null.");
	        }
        }
    }

    private void print(String s) {
        if (outputWriter != null) {
        	outputWriter.print(s);
        }
    }

    private void println(String s) {
        if (outputWriter != null) {
            outputWriter.println(s);
            outputWriter.flush();
        }
    }

    private void printlnError(Object o) {
        if (errorWriter != null) {
            errorWriter.println(o);
        }
    }

    private void flush() {
        if (outputWriter != null) {
            outputWriter.flush();
        }
        if (errorWriter != null) {
            errorWriter.flush();
        }
    }
}
